package xhe.lib.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;
import javax.sql.rowset.CachedRowSet;

import org.apache.ibatis.datasource.pooled.PooledDataSource;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.springframework.util.StringUtils;

import com.sun.rowset.CachedRowSetImpl;

import xhe.entities.ApiEntity;
import xhe.entities.DBEntity;
import xhe.enums.ExecuteEnum;
import xhe.lib.base.YmlUtils;

/**
 * <b> 工具类: </b> 操作数据库厂类
 * 
 * @author xhe
 * @version V1.0.0.0
 * @version --2020年1月15日
 */
public class BaseFactory {
	public DBEntity oDBEntity = new DBEntity();

	private static Map<String, DBEntity> DBEntityList = new HashMap<String, DBEntity>();

	/**
	 * <b> 构造函数: </b> 根据指定的数据库连接类型实例化数据库对象
	 * 
	 * @param dbtype 数据库连接类型
	 */
	public BaseFactory(String dbtype) {
		try {

			if (dbtype.toLowerCase().contains("gaussdb")) {
				// Gauss数据库
				this.oDBEntity = GaussDB.InitConnection(dbtype);

			} else if (dbtype.toLowerCase().contains("sqlserver")) {
				// SQLServer数据库
				this.oDBEntity = SQLServer.InitConnection(dbtype);

			} else if (dbtype.toLowerCase().contains("mysql")) {
				// MySQL数据库
				this.oDBEntity = MySQL.InitConnection(dbtype);
			}

		} catch (Exception ex) {
		}
	}

	/**
	 * <b> 构造函数: </b> 根据接口参数实例化数据库对象
	 * 
	 * @param requestEntity 接口参数类
	 */
	public BaseFactory(ApiEntity requestEntity) {
		oDBEntity.setDBType(requestEntity.getDBType());
		oDBEntity.setDriver(requestEntity.getDBConnection().get("driver-class-name"));
		oDBEntity.setUser(requestEntity.getDBConnection().get("username"));
		oDBEntity.setPwd(requestEntity.getDBConnection().get("password"));
		oDBEntity.setDBUrl(requestEntity.getDBConnection().get("jdbc-url"));
	}

	/**
	 * <b> 方法: </b> 查询结果
	 * 
	 * @author xhe
	 * @param selectSql 查询字符串
	 * @param APIModel  接口属性类
	 * @return ResultSet 查询结果对象
	 * @version V1.0.0.0
	 * @version --2020年1月15日
	 * @version --新增方法
	 * @throws SQLException
	 */
	public CachedRowSet Query(String selectSql, ApiEntity APIModel) throws SQLException {

		Connection oConnection = null;
		Statement oStatement = null;
		ResultSet oResultSet = null;

		try {
			// 加载驱动。
			Class.forName(oDBEntity.getDriver());

			// 创建连接。
			oConnection = DriverManager.getConnection(oDBEntity.getDBUrl(), oDBEntity.getUser(), oDBEntity.getPwd());

			System.out.println("Connection succeed!");

			if (oConnection != null) {
				oStatement = oConnection.createStatement();

				// 结果集
				oResultSet = oStatement.executeQuery(selectSql);

				CachedRowSet oCachedRowSet = new CachedRowSetImpl();
				oCachedRowSet.populate(oResultSet);

				return oCachedRowSet;
			} else {
				return null;
			}

		} catch (Exception e) {

			APIModel.setCode(ExecuteEnum.DBError);
			APIModel.setMessage(e.getMessage());
			e.printStackTrace();
			return null;
		} finally {
			if (oResultSet != null) {
				oResultSet.close();
			}
			if (oStatement != null) {
				oStatement.close();
			}
			if (oConnection != null) {
				oConnection.close();
			}
		}
	}

	public ResultSet QueryEx(String selectSql, ApiEntity APIModel) throws SQLException {

		Connection oConnection = null;
		Statement oStatement = null;
		ResultSet oResultSet = null;

		try {
			// 加载驱动。
			Class.forName(oDBEntity.getDriver());

			// 创建连接。
			oConnection = DriverManager.getConnection(oDBEntity.getDBUrl(), oDBEntity.getUser(), oDBEntity.getPwd());

			System.out.println("Connection succeed!");

			if (oConnection != null) {
				oStatement = oConnection.createStatement();

				// 结果集
				oResultSet = oStatement.executeQuery(selectSql);

				return oResultSet;
			} else {
				return null;
			}

		} catch (Exception e) {

			APIModel.setCode(ExecuteEnum.DBError);
			APIModel.setMessage(e.getMessage());
			e.printStackTrace();
			return null;
		} finally {

//			if (oStatement != null) {
//				oStatement.close();
//			}
			if (oConnection != null) {
				oConnection.close();
			}
		}
	}

	/**
	 * <b> 方法: </b> 执行结果
	 * 
	 * @author xhe
	 * @param selectSql 查询字符串
	 * @param APIModel  接口属性类
	 * @return int 执行结果条数
	 * @version V1.0.0.0
	 * @version --2020年1月15日
	 * @version --新增方法
	 * @throws SQLException
	 */
	public int Execute(String selectSql, ApiEntity APIModel) throws SQLException {

		Connection oConnection = null;
		Statement oStatement = null;

		try {
			// 加载驱动。
			Class.forName(oDBEntity.getDriver());

			// 创建连接。
			oConnection = DriverManager.getConnection(oDBEntity.getDBUrl(), oDBEntity.getUser(), oDBEntity.getPwd());

			System.out.println("Connection succeed!");

			if (oConnection != null) {
				oStatement = oConnection.createStatement();

				// 结果集
				int iResult = oStatement.executeUpdate(selectSql);

				// oConnection.close();

				return iResult;
			} else {
				return -99;
			}

		} catch (Exception e) {
			APIModel.setCode(ExecuteEnum.DBError);
			APIModel.setMessage(e.getMessage());
			e.printStackTrace();
			return -1;
		} finally {

			if (oStatement != null) {
				oStatement.close();
			}
			if (oConnection != null) {
				oConnection.close();
			}

		}
	}

	public static <T> boolean add(String dbType, Class<T> mapper, Object clazz) {
		try {
			DBEntity oDBEntity = getConnection(dbType);

			if (oDBEntity != null) {

				DataSource dataSource = new PooledDataSource(oDBEntity.getDriver(), oDBEntity.getDBUrl(),
						oDBEntity.getUser(), oDBEntity.getPwd());

				// 创建事务
				TransactionFactory transactionFactory = new JdbcTransactionFactory();

				Environment environment = new Environment("development", transactionFactory, dataSource);

				Configuration configuration = new Configuration(environment);

				// 映射mapper类
				configuration.addMapper(mapper);

				SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
				System.out.println(sqlSessionFactory);

				SqlSession session = null;

				try {
					session = sqlSessionFactory.openSession();
					session.insert("add", clazz);
					session.commit();

					return true;
				} catch (Exception ex) {
					ex.printStackTrace();
					return false;
				} finally {
					if (session != null) {
						session.close();
					}
				}
			} else {
				return false;
			}
		} catch (Exception ex) {
			return false;
		}
	}

	private static DBEntity getConnection(String dbType) {
		try {
			if (!DBEntityList.containsKey(dbType)) {
				Map<String, String> oDataSourceMap = YmlUtils.getYmlMapsByFileName("application.yml", "spring",
						"datasource", dbType.toLowerCase());

				DBEntity oDBEntity = new DBEntity();
				oDBEntity.setDriver(oDataSourceMap.get("driver-class-name"));
				oDBEntity.setUser(oDataSourceMap.get("username"));
				oDBEntity.setPwd(oDataSourceMap.get("password"));
				oDBEntity.setDBUrl(oDataSourceMap.get("jdbc-url"));

				DBEntityList.put(dbType, oDBEntity);

				return oDBEntity;
			} else {
				return DBEntityList.get(dbType);
			}
		} catch (Exception ex) {
			return null;
		}
	}

	/**
	 * <b> Method: </b> 拼装带条数的SQL语句
	 * 
	 * @author xhe
	 * @param selectSql 查询字符串
	 * @param selectNum 查询条数
	 * @return String
	 * @version V1.0.0.0
	 * @version --2020年7月22日
	 * @version --新增方法
	 * @version V1.0.1.0
	 * @version --2020年7月22日
	 * @version --新增MySQL查询方式
	 */
	public String GetSqlByNum(String selectSql, String selectNum) {
		try {
			if (oDBEntity.getDBUrl().contains("gaussdb") || oDBEntity.getDBUrl().contains("mysql")) {
				String strSql = selectSql + " limit " + selectNum;

				return strSql;
			} else if (oDBEntity.getDBUrl().contains("sqlserver")) {
				String strSql = selectSql.toLowerCase().replaceFirst("select", "select top " + selectNum);

				return strSql;
			} else {
				return selectSql;
			}
		} catch (Exception e) {
			return selectSql;
		}
	}

	/**
	 * <b> Method: </b> 拼装带日期格式的SQL语句
	 * 
	 * @author xhe
	 * @param selectDate 查询字符串
	 * @return String SQL语句
	 * @version V1.0.0.0
	 * @version --2020年1月15日
	 * @version --新增方法
	 * @version V1.0.1.0
	 * @version --2020年7月22日
	 * @version --新增MySQL匹配方式
	 */
	public String GetSqlByDate(String selectDate) {

		String selectSql = "";

		try {
			if (oDBEntity.getDBUrl().contains("gaussdb")) {
				selectSql = "date'" + selectDate + "'";
			} else if (oDBEntity.getDBUrl().contains("sqlserver")) {
				selectSql = "'" + selectDate + "'";
			} else if (oDBEntity.getDBUrl().contains("mysql")) {
				selectSql = "timestamp'" + selectDate + "'";
			} else {

			}
			return selectSql;
		} catch (Exception e) {
			return selectSql;
		}
	}

	/**
	 * <b> Method: </b> 拼装带分页的SQL语句
	 * 
	 * @author xhe
	 * @param tableName 表名
	 * @param filter    查询条件
	 * @param sortName  排序字段
	 * @param sortOrder 排序类型
	 * @param pageIndex 页码
	 * @param pageSize  分页数量
	 * @return String SQL语句
	 * @version V1.0.0.0
	 * @version --2020年1月15日
	 * @version --新增方法
	 * @version V1.0.1.0
	 * @version --2020年7月22日
	 * @version --新增MySQL查询方式
	 */
	public String GetSqlByPage(String tableName, String filter, String sortName, String sortOrder, int pageIndex,
			int pageSize) {
		try {
			String strSql = "";
			if (oDBEntity.getDBUrl().contains("gaussdb")) {

				strSql += "select ";
				strSql += "    * ";
				strSql += "from ( ";
				strSql += "    select ";
				strSql += "        row_number() over(order by " + sortName + " " + sortOrder + ") as rownumber,* ";
				strSql += "    from ";
				strSql += "        " + tableName + " ";
				strSql += "where ";
				strSql += "    1=1 " + filter + " ";
				strSql += ") temp_row ";
				strSql += "where ";
				strSql += "    rownumber>(" + pageIndex + "-1)*" + pageSize + " ";
				strSql += "    fetch first " + pageSize + " rows only";

			} else if (oDBEntity.getDBUrl().contains("sqlserver")) {

				strSql += "select ";
				strSql += "    top (select " + pageSize + ") * ";
				strSql += "from ";
				strSql += "( ";
				strSql += "    select ";
				strSql += "    	    row_number() over(order by " + sortName + " " + sortOrder + ") as rownumber,* ";
				strSql += "	   from ";
				strSql += "		    " + tableName + " ";
				strSql += "	   where ";
				strSql += "		    1=1 " + filter + " ";
				strSql += ") temp_row ";
				strSql += "where ";
				strSql += "	   rownumber>(" + pageIndex + "-1)*" + pageSize + "";

			} else if (oDBEntity.getDBUrl().contains("mysql")) {

				strSql += "select ";
				strSql += "    * ";
				strSql += "from ";
				strSql += "( ";
				strSql += "    select ";
				strSql += "    	    @rownum := @rownum + 1 as rownumber,a.* ";
				strSql += "	   from ";
				strSql += "		    " + tableName + " a ";
				strSql += "		    (select @rownum := 0) myTempTable ";
				strSql += "	   where ";
				strSql += "		    1=1 " + filter + " ";
				strSql += "	   order by " + sortName + " " + sortOrder + "";
				strSql += ") temp_row ";
				strSql += "where ";
				strSql += "	   rownumber>(" + pageIndex + "-1)*" + pageSize + "";
				strSql += "    limit " + pageSize;

			} else {
			}
			return strSql;
		} catch (Exception e) {
			return "";
		}
	}

	/**
	 * <b> Method: </b> 拼装带分页的SQL语句
	 * 
	 * @author xhe
	 * @param tableName 表名
	 * @param columns   查询字段
	 * @param filter    查询条件
	 * @param sortName  排序字段
	 * @param sortOrder 排序类型
	 * @param pageIndex 页码
	 * @param pageSize  分页数量
	 * @return String SQL语句
	 * @version V1.0.0.0
	 * @version --2020年1月15日
	 * @version --新增方法
	 * @version V1.0.1.0
	 * @version --2020年7月22日
	 * @version --新增MySQL查询方式
	 */
	public String GetSqlByPage(String tableName, String columns, String filter, String sortName, String sortOrder,
			int pageIndex, int pageSize) {
		try {
			String strSql = "";
			if (oDBEntity.getDBUrl().contains("gaussdb")) {

				strSql += "select ";
				strSql += "    " + columns + " ";
				strSql += "from ( ";
				strSql += "    select ";
				strSql += "        row_number() over(order by " + sortName + " " + sortOrder + ") as rownumber,* ";
				strSql += "    from ";
				strSql += "        " + tableName + " ";
				strSql += "where ";
				strSql += "    1=1 " + filter + " ";
				strSql += ") temp_row ";
				strSql += "where ";
				strSql += "    rownumber>(" + pageIndex + "-1)*" + pageSize + " ";
				strSql += "    fetch first " + pageSize + " rows only";

			} else if (oDBEntity.getDBUrl().contains("sqlserver")) {

				strSql += "select ";
				strSql += "    top (select " + pageSize + ") " + columns + " ";
				strSql += "from ";
				strSql += "( ";
				strSql += "    select ";
				strSql += "    	    row_number() over(order by " + sortName + " " + sortOrder + ") as rownumber,* ";
				strSql += "	   from ";
				strSql += "		    " + tableName + " ";
				strSql += "	   where ";
				strSql += "		    1=1 " + filter + " ";
				strSql += ") temp_row ";
				strSql += "where ";
				strSql += "	    rownumber>(" + pageIndex + "-1)*" + pageSize + "";

			} else if (oDBEntity.getDBUrl().contains("mysql")) {

				strSql += "select ";
				strSql += "    " + columns + " ";
				strSql += "from ";
				strSql += "( ";
				strSql += "    select ";
				strSql += "    	    @rownum := @rownum + 1 as rownumber,a.* ";
				strSql += "	   from ";
				strSql += "		    " + tableName + " a, ";
				strSql += "		    (select @rownum := 0) myTempTable ";
				strSql += "	   where ";
				strSql += "		    1=1 " + filter + " ";
				strSql += "	   order by " + sortName + " " + sortOrder + "";
				strSql += ") temp_row ";
				strSql += "where ";
				strSql += "	   rownumber>(" + pageIndex + "-1)*" + pageSize + "";
				strSql += "    limit " + pageSize;

			} else {
			}
			return strSql;
		} catch (Exception e) {
			return "";
		}
	}

	/**
	 * <b> Method: </b> 拼装带分页的SQL语句
	 * 
	 * @author xhe
	 * @param tableName    表名
	 * @param columns      查询字段
	 * @param filter       查询条件
	 * @param groupColumns 分组字段
	 * @param orderColumns 排序字段
	 * @param pageIndex    页码
	 * @param pageSize     分页数量
	 * @return String SQL语句
	 * @version V1.0.0.0
	 * @version --2020年1月15日
	 * @version --新增方法
	 * @version V1.0.1.0
	 * @version --2020年9月07日
	 * @version --新增MySQL查询方式
	 */
	public String GetSqlByGroup(String tableName, String columns, String filter, String groupColumns,
			String orderColumns, int pageIndex, int pageSize) {
		try {
			String strSql = "";
			if (oDBEntity.getDBUrl().contains("gaussdb")) {

				strSql += "select ";
				strSql += "    " + columns + " ";
				strSql += "from ( ";
				strSql += "    select ";
				strSql += "        row_number() over(order by " + orderColumns + ") as rownumber,* ";
				strSql += "    from ";
				strSql += "        " + tableName + " ";
				strSql += "where ";
				strSql += "    1=1 " + filter + " ";
				strSql += ") temp_row ";
				strSql += "where ";
				strSql += "    rownumber>(" + pageIndex + "-1)*" + pageSize + " ";
				strSql += "group by " + groupColumns + " ";
				strSql += "    fetch first " + pageSize + " rows only";

			} else if (oDBEntity.getDBUrl().contains("sqlserver")) {

				strSql += "select ";
				strSql += "    top (select " + pageSize + ") " + columns + " ";
				strSql += "from ";
				strSql += "( ";
				strSql += "    select ";
				strSql += "    	    row_number() over(order by " + orderColumns + ") as rownumber,* ";
				strSql += "	   from ";
				strSql += "		    " + tableName + " ";
				strSql += "	   where ";
				strSql += "		    1=1 " + filter + " ";
				strSql += ") temp_row ";
				strSql += "where ";
				strSql += "	    rownumber>(" + pageIndex + "-1)*" + pageSize + " ";
				strSql += "group by " + groupColumns + " ";

			} else if (oDBEntity.getDBUrl().contains("mysql")) {

				strSql += "select ";
				strSql += "    " + columns + " ";
				strSql += "from ";
				strSql += "( ";
				strSql += "    select ";
				strSql += "    	    @rownum := @rownum + 1 as rownumber,a.* ";
				strSql += "	   from ";
				strSql += "		    " + tableName + " a, ";
				strSql += "		    (select @rownum := 0) myTempTable ";
				strSql += "	   where ";
				strSql += "		    1=1 " + filter + " ";
				strSql += "	   order by " + orderColumns + "";
				strSql += ") temp_row ";
				strSql += "where ";
				strSql += "	   rownumber>(" + pageIndex + "-1)*" + pageSize + " ";
				strSql += "group by " + groupColumns + " ";
				strSql += "limit " + pageSize;

			} else {
			}
			return strSql;
		} catch (Exception e) {
			return "";
		}
	}

	/**
	 * <b> Method: </b> 拼装带分页的SQL语句
	 * 
	 * @author xhe
	 * @param tableName    表名
	 * @param joinUnion    关联语句
	 * @param columns      查询字段
	 * @param filter       查询条件
	 * @param groupColumns 分组字段
	 * @param orderColumns 排序字段
	 * @param pageIndex    页码
	 * @param pageSize     分页数量
	 * @return String SQL语句
	 * @version V1.0.0.0
	 * @version --2020年1月15日
	 * @version --新增方法
	 * @version V1.0.1.0
	 * @version --2020年9月07日
	 * @version --新增MySQL查询方式
	 */
	public String GetSqlByJoin(String tableName, String joinUnion, Map<String, List<String>> columns, String filter,
			String groupColumns, String orderColumns, int pageIndex, int pageSize) {
		try {
			String strSql = "";
			if (oDBEntity.getDBUrl().contains("gaussdb")) {

				strSql += "select ";
				strSql += "    " + columns + " ";
				strSql += "from ( ";
				strSql += "    select ";
				strSql += "        row_number() over(order by " + orderColumns + ") as rownumber,* ";
				strSql += "    from ";
				strSql += "        " + tableName + " ";
				strSql += "where ";
				strSql += "    1=1 " + filter + " ";
				strSql += ") temp_row ";
				strSql += "where ";
				strSql += "    rownumber>(" + pageIndex + "-1)*" + pageSize + " ";
				strSql += "group by " + groupColumns + " ";
				strSql += "    fetch first " + pageSize + " rows only";

			} else if (oDBEntity.getDBUrl().contains("sqlserver")) {

				strSql += "select ";
				strSql += "    top (select " + pageSize + ") " + columns + " ";
				strSql += "from ";
				strSql += "( ";
				strSql += "    select ";
				strSql += "    	    row_number() over(order by " + orderColumns + ") as rownumber,* ";
				strSql += "	   from ";
				strSql += "		    " + tableName + " ";
				strSql += "	   where ";
				strSql += "		    1=1 " + filter + " ";
				strSql += ") temp_row ";
				strSql += "where ";
				strSql += "	    rownumber>(" + pageIndex + "-1)*" + pageSize + " ";
				strSql += "group by " + groupColumns + " ";

			} else if (oDBEntity.getDBUrl().contains("mysql")) {

				String strKeys = "";
				String strCols = "";
				for (String strKey : columns.keySet()) {
					strKeys += "    temp_row." + strKey + ",";
					strCols += "    " + columns.get(strKey).get(0) + " " + strKey + ",";
				}

				strKeys = strKeys.substring(0, strKeys.length() - 1);
				strCols = strCols.substring(0, strCols.length() - 1);

				strSql += "select ";
				strSql += "	   " + strKeys + " ";
				strSql += "from ";
				strSql += "( ";
				strSql += "    select ";
				strSql += "    	    @rownum := @rownum + 1 as rownumber,";
				strSql += "	   " + strCols + " ";
				strSql += "	   from ";
				strSql += "		    (" + tableName + ", ";
				strSql += "		    (select @rownum := 0) myTempTable) ";
				strSql += joinUnion;
				strSql += "	   where ";
				strSql += "		    1=1 " + filter + " ";

				if (!StringUtils.isEmpty(orderColumns)) {
					strSql += "order by ";
					strSql += "    " + orderColumns + " ";
				}

				strSql += ") temp_row ";
				strSql += "where ";
				strSql += "	   rownumber>(" + pageIndex + "-1)*" + pageSize + " ";

				if (!StringUtils.isEmpty(groupColumns)) {
					strSql += "group by ";
					strSql += "    " + groupColumns + " ";
				}

				strSql += "limit " + pageSize;

			} else {
			}
			return strSql;
		} catch (Exception e) {
			return "";
		}
	}

	/**
	 * <b> Method: </b> 获取总条数语句
	 * 
	 * @author xhe
	 * @param tableName 表名
	 * @param joinUnion 关联语句
	 * @param columns   查询字段
	 * @param filter    查询条件
	 * @return String SQL语句
	 * @version V1.0.0.0
	 * @version --2020年9月22日
	 * @version --新增方法
	 */
	public String GetSqlByCount(String tableName, String joinUnion, Map<String, List<String>> columns, String filter) {
		try {
			String strSql = "";

			String strCols = "";
			for (String strKey : columns.keySet()) {
				strCols += "    " + columns.get(strKey).get(0) + " " + strKey + ",";
			}

			strCols = strCols.substring(0, strCols.length() - 1);

			strSql += "select ";
			strSql += "	   count(1) totalNum ";
			strSql += "from ";
			strSql += "( ";
			strSql += "    select ";
			strSql += "	       " + strCols + " ";
			strSql += "	   from ";
			strSql += "		    " + tableName + " ";
			strSql += "" + joinUnion;
			strSql += ") temp_row ";
			strSql += "where ";
			strSql += "		1=1 " + filter + " ";

			return strSql;
		} catch (Exception e) {
			return "";
		}
	}

	/**
	 * <b> 方法: </b> 拼装带排序的SQL语句
	 * 
	 * @param tableName 表名
	 * @param columns   查询字段
	 * @param filter    查询条件
	 * @param sortName  排序字段
	 * @param sortOrder 排序类型
	 * @return String SQL语句
	 * @version V1.0.0.0
	 * @version --2020年1月15日
	 * @version --新增方法
	 */
	public String GetSql(String tableName, String columns, String filter, String sortName, String sortOrder) {
		try {
			String strSql = "";
			strSql += "select ";
			strSql += "    " + columns + " ";
			strSql += "from ";
			strSql += "    " + tableName + " ";
			strSql += "where ";
			strSql += "    1=1 " + filter + " ";

			if (!StringUtils.isEmpty(sortName)) {
				strSql += "order by ";
				strSql += "    " + sortName + " " + sortOrder;
			}

			return strSql;
		} catch (Exception e) {
			return "";
		}
	}

	/**
	 * <b> 方法: </b> 拼装带排序的SQL语句
	 * 
	 * @param tableName 表名
	 * @param columns   查询字段
	 * @param filter    查询条件
	 * @param groupName 分组字段
	 * @param orderName 排序类型
	 * @return String SQL语句
	 * @version V1.0.0.0
	 * @version --2020年9月7日
	 * @version --新增方法
	 */
	public String GetSqlByGroup(String tableName, String columns, String filter, String groupName, String orderName) {
		try {
			String strSql = "";
			strSql += "select ";
			strSql += "    " + columns + " ";
			strSql += "from ";
			strSql += "    " + tableName + " ";
			strSql += "where ";
			strSql += "    1=1 " + filter + " ";

			if (!StringUtils.isEmpty(groupName)) {
				strSql += "group by ";
				strSql += "    " + groupName + " ";
			}

			if (!StringUtils.isEmpty(orderName)) {
				strSql += "order by ";
				strSql += "    " + orderName + " ";
			}

			return strSql;
		} catch (Exception e) {
			return "";
		}
	}

	/**
	 * <b> 方法: </b> 拼装带排序的SQL语句
	 * 
	 * @param tableName 表名
	 * @param joinUnion 关联语句
	 * @param columns   查询字段
	 * @param filter    查询条件
	 * @param groupName 分组字段
	 * @param orderName 排序类型
	 * @return String SQL语句
	 * @version V1.0.0.0
	 * @version --2020年9月22日
	 * @version --新增方法
	 */
	public String GetSqlByJoin(String tableName, String joinUnion, Map<String, List<String>> columns, String filter,
			String groupName, String orderName) {
		try {

			String strKeys = "";
			String strCols = "";
			for (String strKey : columns.keySet()) {
				strKeys += "    temp_row." + strKey + ", ";
				// strCols += " " + columns.get(strKey).get(0) + ",";
				strCols += "    " + columns.get(strKey).get(0) + " " + strKey + ",";
			}

			strKeys = strKeys.substring(0, strKeys.length() - 1);
			strCols = strCols.substring(0, strCols.length() - 1);

			String strSql = "";
			strSql += "select ";
			strSql += strCols + " ";
			strSql += "from ";
			strSql += "    " + tableName + " ";
			strSql += joinUnion + " ";
			strSql += "where ";
			strSql += "    1=1 " + filter + " ";

			if (!StringUtils.isEmpty(groupName)) {
				strSql += "group by ";
				strSql += "    " + groupName + " ";
			}

			if (!StringUtils.isEmpty(orderName)) {
				strSql += "order by ";
				strSql += "    " + orderName + " ";
			}

			return strSql;
		} catch (Exception e) {
			return "";
		}
	}

	/**
	 * <b> 方法: </b> 拼装带排序的SQL语句
	 * 
	 * @param tableName 表名
	 * @param columns   查询字段
	 * @param filter    查询条件
	 * @param sortName  排序字段
	 * @param sortOrder 排序类型
	 * @return String SQL语句
	 * @version V1.0.0.0
	 * @version --2021年5月20日
	 * @version --新增方法
	 */
	public String GetSql(String tableName, Map<String, List<String>> columns, String filter, String sortName,
			String groupName, String joinUnion) {
		try {

			String strCols = "";

			for (String strKey : columns.keySet()) {

				// strCols += " " + columns.get(strKey).get(0) + " " + strKey + ",";
				strCols += " " + columns.get(strKey).get(0) + " " + strKey + ",";
				// strCols += " " + columns.get(strKey).get(0) + ",";
			}

			strCols = strCols.substring(0, strCols.length() - 1);

			String strSql = "";
			strSql += "select ";
			strSql += "    " + strCols + " ";
			strSql += "from ";
			strSql += "    " + tableName + " ";
			if (!StringUtils.isEmpty(joinUnion)) {
				strSql += joinUnion + " ";
			}
			strSql += "where ";
			strSql += "    1=1 " + filter + " ";

			if (!StringUtils.isEmpty(groupName)) {
				strSql += "group by ";
				strSql += "    " + groupName + " ";
			}

			if (!StringUtils.isEmpty(sortName)) {
				strSql += "order by ";
				strSql += "    " + sortName;
			}

			return strSql;
		} catch (Exception e) {
			return "";
		}
	}

	/**
	 * <b> 方法: </b> 比较查询列是否包含在聚合列中
	 * 
	 * @param columnName 列名
	 * @param groupName  聚合列的集合
	 * @return String true/false
	 * @version V1.0.0.0
	 * @version --2020年9月22日
	 * @version --新增方法
	 */
	public static Boolean compareColumn(String columnName, String groupName) {
		try {
			String[] aryGroupName = groupName.split(",");

			for (String strGroupName : aryGroupName) {
				if (strGroupName.toLowerCase().equals(columnName.toLowerCase())) {
					return true;

				}
			}

			return false;

		} catch (Exception e) {
			return false;
		}
	}
}
